﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using MySql.Data.MySqlClient;

namespace Avenger
{
    public class TweetUser
    {
        public long id;
        public int followers_count;
        public int friends_count;
        public int statuses_count;
        public bool verified;
    }
    public class Tweet
    {
        public long id;
        public string text;
        public int reposts_count;
        public int comments_count;
        public DateTime created_at;
        public TweetUser user;
        public Tweet()
        {
            this.created_at = new DateTime();
            this.user = new TweetUser();
        }
    }

    public class MySqlManager
    {
        #region var
        private const string myConnectionString = "server=127.0.0.1;uid=root;pwd=seasonlove";
        private MySqlConnection mySqlConnection;
        #endregion
        private static MySqlManager mySqlManager;
        public static MySqlManager Instance
        {
            get
            {
                if (mySqlManager == null)
                {
                    mySqlManager = new MySqlManager();
                    return mySqlManager;
                }
                return mySqlManager;
            }
        }

        public bool getAllTweet(out List<Tweet> tweetList)
        {
            tweetList = new List<Tweet>();
            try
            {
                DataSet data = new DataSet();
                MySqlDataAdapter adapter = new MySqlDataAdapter(); 
                string cmdString = @"SELECT * FROM Tweets LIMIT 0,300000";
                MySqlCommand cmd = new MySqlCommand(cmdString, mySqlConnection);
                adapter.SelectCommand = cmd;
                adapter.Fill(data);
                if (data.Tables.Count == 0)
                    return false;
                DataTable tweets = data.Tables[0];
                long count = 0;
                foreach (DataRow row in tweets.Rows)
                {
                    Tweet tweet = new Tweet();
                    foreach (DataColumn column in tweets.Columns)
                    {       
                        switch (column.ColumnName)
                        {
                            case "id":
                                tweet.id = long.Parse(row[column].ToString());
                                break;
                            case "text":
                                tweet.text = row[column].ToString();
                                break;
                            case "reposts_count":
                                tweet.reposts_count = int.Parse(row[column].ToString());
                                break;
                            case "comments_count":
                                tweet.comments_count = int.Parse(row[column].ToString());
                                break;
                            case "created_at":
                                tweet.created_at = DateTime.Parse(row[column].ToString());
                                break;
                            case "user_id":
                                tweet.user.id = long.Parse(row[column].ToString());
                                break;
                            case "user_fllowers_count":
                                tweet.user.followers_count = int.Parse(row[column].ToString());
                                break;  
                            case "user_friends_count":
                                tweet.user.friends_count = int.Parse(row[column].ToString());
                                break;
                            case "user_statuses_count":
                                tweet.user.statuses_count = int.Parse(row[column].ToString());
                                break;
                            case "verified":
                                tweet.user.verified = row[column].ToString() == "0" ? true : false;
                                break;
                        }
                    }
                    if (string.IsNullOrEmpty(tweet.text))
                        count++;
                    tweetList.Add(tweet);
                }
                return true;
            }
            catch (Exception e)
            {
                return false;
            }
        }
        public bool connectMySqlServer()
        {
            try
            {
                mySqlConnection = new MySqlConnection(myConnectionString);
                mySqlConnection.Open();
                if (mySqlConnection.State == System.Data.ConnectionState.Open)
                    return true;
                else
                    return false;
            }
            catch (Exception e)
            {
                return false;
                //TODO:handel exception
            }
        }
        public bool buildDataBase()
        {
            try
            {
                string cmdString = "DROP DATABASE IF EXISTS Avenger;CREATE DATABASE Avenger;USE Avenger;";
                MySqlCommand cmd = new MySqlCommand(cmdString, mySqlConnection);
                cmd.ExecuteScalar();
                return true;
            }
            catch (Exception e)
            {
                return false;
            }            
        }
        public bool buildTable()
        {
            try
            {
                string cmdString = "CREATE TABLE Tweets(id BIGINT NOT NULL UNIQUE,text TEXT,reposts_count INT,comments_count INT,created_at DATETIME,user_id BIGINT,user_fllowers_count INT,user_friends_count INT,user_statuses_count INT,verified TINYINT(1));";
                MySqlCommand cmd = new MySqlCommand(cmdString, mySqlConnection);
                cmd.ExecuteScalar();
                return true;
            }
            catch (Exception e)
            {
                return false;
            }
        }
        public bool useAvenger()
        {
            try
            {
                string cmdString = "USE Avenger;";
                MySqlCommand cmd = new MySqlCommand(cmdString, mySqlConnection);
                cmd.ExecuteScalar();
                return true;
            }
            catch (Exception e)
            {
                return false;
            }
        }
        public bool insertTweet(Tweet tweet)
        {
            try
            {
                string cmdString = "INSERT INTO Tweets(id,text,reposts_count,comments_count,created_at,user_id,user_fllowers_count,user_friends_count,user_statuses_count,verified) VALUES(" + tweet.id.ToString() + "," + "'" + tweet.text + "'" + "," + tweet.reposts_count.ToString() + "," + tweet.comments_count.ToString() + "," + "\"" + tweet.created_at.ToString() + "\"" + "," + tweet.user.id.ToString() + "," + tweet.user.followers_count.ToString() + "," + tweet.user.friends_count.ToString() + "," + tweet.user.statuses_count.ToString() + "," + (tweet.user.verified ? "1" : "0") + ")";
                MySqlCommand cmd = new MySqlCommand(cmdString, mySqlConnection);
                cmd.ExecuteScalar();
                return true;
            }
            catch (Exception e)
            {
                return false;
            }
        }
        public Tweet praseTweet(NetDimension.Weibo.Entities.status.Entity status)
        {
            if (status == null)
                return null;
            Tweet tweet = new Tweet();
            tweet.id = long.Parse(status.ID);//TODO: parse fail
            tweet.text = status.Text;
            tweet.reposts_count = status.RepostsCount;
            tweet.comments_count = status.CommentsCount;
            tweet.created_at = praseStatusTime(status.CreatedAt);
            tweet.user.id = long.Parse(status.User.ID);
            tweet.user.followers_count = status.User.FollowersCount;
            tweet.user.friends_count = status.User.FriendsCount;
            tweet.user.statuses_count = status.User.StatusesCount;
            tweet.user.verified = status.User.Verified;

            return tweet;
        }
        public DateTime praseStatusTime(string time)
        {
            if (string.IsNullOrEmpty(time))
                return new DateTime();
            DateTime dateTime;
            string[] times = time.Split(new Char[] { ' ' }, StringSplitOptions.RemoveEmptyEntries);
            int month = 0;
            switch (times[1])
            {
                case "Jan":
                    month = 1;
                    break;
                case "Feb":
                    month = 2;
                    break;
                case "Mar":
                    month = 3;
                    break;
                case "Apr":
                    month = 4;
                    break;
                case "May":
                    month = 5;
                    break;
                case "Jun":
                    month = 6;
                    break;
                case "Jul":
                    month = 7;
                    break;
                case "Aug":
                    month = 8;
                    break;
                case "Sep":
                    month = 9;
                    break;
                case "Oct":
                    month = 10;
                    break;
                case "Nov":
                    month = 11;
                    break;
                case "Dec":
                    month = 12;
                    break;
            }
            string[] hourMinuteSecond = times[3].Split(new Char[] { ':' }, StringSplitOptions.RemoveEmptyEntries);

            dateTime = new DateTime(int.Parse(times[5]), month, int.Parse(times[2]), int.Parse(hourMinuteSecond[0]), int.Parse(hourMinuteSecond[1]), int.Parse(hourMinuteSecond[2]));
            return dateTime;
        }
    }
}
